This invention relates to computer database searching. More specifically, this invention relates to the formulation and the efficient execution of complex database queries using a single query expression against the database.
The amount of multimedia data available in electronic format is ever increasing. The cost of loading such data into a database is quite high and it is desirable that this task does not have to be repeated when writing different applications which use such data. Furthermore it is desirable to be able to add different databases to a system without the need to rewrite the application in a major way. In general, a relational database comprises tables which contain records that have a zero-to-many relationship to records in other tables. A query is formulated against one or many tables as appropriate and upon execution returns a set of records. To get the desired user query resolved, several sub-queries queries may have to be formulated, and then the results of each of these sub-queries queries combined.
For example, assume a DB2 (IBM(trademark)) database which is populated with several tables. Each table has many records (rows) and many columns. A user can pose a query like: find all the DEALERS which have PINK CADILLACS in STOCK (referred to as parametric query). In this example, there are at least the following columns in the database: DEALERS, COLOR, MAKE, AVAILABILITY. (This is a straightforward database example). Now lets assume that with each row in the table, there is also a textual description column. Some databases like DB2 have a special method (called DB2 TextExtenders, IBM(trademark)) to search such textual columns for the occurrence of a string or a logical expression of words (e.(g. USED or NEW). A multi-search query would for instance extend the above query by adding the query condition xe2x80x9cUSED or NEWxe2x80x9d. One way to execute the query is to first execute the parametric query and store its results in an application, then execute the textual query and store its result in the application. The application then combines the results of the two sub-queries queries (e.g. parametric and textual) for a final result. The problem is that each of the sub-queries queries may return a big result set, which is expensive to transmit from the database to the application. Furthermore, combining results from the sub-queries queries is expensive.
An object of this invention is an improved database query system and method.
An object of this invention is novel object oriented query data model.
An object of this invention is to formulate a single valid query against a relational database which eliminates the use of intermediate result sets and uses the database for performance optimization while maintaining some flexibility to perform some other optimization.
An object of this invention is to formulate a single valid query as described above which returns in addition to rows from tables from the database also returns computed values in some of the result columns.
In a preferred embodiment, the present invention works in a computer system having one or more central processing units and one or more memories. The computer system has an interface to one or more databases, one or more base query objects, one or more query objects, one or more compound queries, one or more annotator objects and one of more graphical user interfaces (GUI""s). The base query objects have one or more base query object methods, one or more base variables, and one or more base objects, one or more of the base query object methods being specific to the specific database and capable of querying the specific database. Each of the query objects derived from one of the base objects, and each of the query objects has a query type, one or more query object methods, one or more query object variables, and one or more query object objects. Each query object method is capable of querying a specific database to obtain a type result having the respective type. The compound query has one or more compound query methods, one or more compound query variables, and one or more compound query object objects. The operator objects, are derived from one of the base query objects that are used with the specific database. The graphical user interface (GUI) has one or more query elements with one or more operators. Each query element is one of the query types but being database independent, the query elements, operators, and conditions are user selectable. The process that, for each query element, operates on the query object with the same type as the query element to create an instance of the query object with the query element as one of the query object variables, creates one or more operator object instances from the operator objects corresponding to the operators, and operates on the compound query object to create a compound query object instance. The compound query object instance uses the instances and the operator object instances to create a query expression for the specific database. Therefore the input in the GUI is translated into a single compound query object.
There are different methods to evaluate a complex user query. In the present invention we propose a very efficient way of translating a complex user query into a single query string in a structured query language.
The query objects are created by a client process. The query objects have one or more sub-query objects and one or more execute methods that are capable of operating on their respective query object to produce one or more query expressions. All of the execute methods are capable of producing the respective query expression that is compatible with a structured query language. A compound query contains one or more boolean expressions of one or more of the query objects. The compound query has one or more compound execute methods which invoke one or more the execute methods of each of the query objects. Each of the execute methods returns their respective query expression and the compound execute method uses one or more common table expressions to combine the query expressions to form a single compound query expression that represents the boolean expression. This single expression can be executed against a database to return a result without executing any of the query expressions against the database individually.